import pandas as pd
from bs4 import BeautifulSoup
from selenium import webdriver
from selenium.webdriver.common.by import By
import time
import requests
import os
import xlrd
import openpyxl as opp
from openpyxl.styles import PatternFill,Font,Border,Side,Alignment
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from io import StringIO


chrome_options=webdriver.ChromeOptions()
#使用headless无界面浏览器模式
chrome_options.add_argument('--headless')
chrome_options.add_argument('--disable-gpu')
driver=webdriver.Chrome(options=chrome_options)
#driver=webdriver.Chrome(options=chrome_options,executable_path=r"C:\Program Files\Google\Chrome\Application\chromedriver.exe")


data = xlrd.open_workbook(r'D:\数据\股票池2024.xls',formatting_info=True)#复制时保留格式
worksheet = data.sheets()[5]
name_list=worksheet.col_values(0)#列
#print(name_list)
while "" in name_list:
    name_list.remove("")

#name_list=['洪城环境','京基智农','水井坊']
for name in name_list:
    try:
        #遍历name_list中的每一个内容
        print(name)
        url0='https://finance.sina.com.cn/realstock/company/sz000651/nc.shtml'
        driver.get(url0)
        driver.find_element(By.ID,"suggest_top").send_keys(name)
        
        driver.find_element(By.CLASS_NAME,"btn").click()
        #driver.find_element_by_id("suggest_top").send_keys(name)
        #driver.find_element_by_class_name("btn").click()
        
        time.sleep(5)
        html=BeautifulSoup(driver.page_source,"html.parser")
        try:
            Code=html.find('h1',id='stockName').get_text()
            code=Code[-4:-2]+Code[-11:-5]
            print(code)    
        except Exception as cuowu:
            print(cuowu.args)
            continue


        Pr=driver.find_element(By.XPATH,'//*[@id="hqDetails"]/table/tbody/tr[5]/td[1]').text
        if '亿' in Pr:
            Pr=eval(Pr.replace('亿','*100000000'))
        elif '万' in Pr:
            Pr=eval(Pr.replace('万', '*10000'))


        
        #新建文件夹
        path='D:\\数据\\2025\\'+name
        
        if os.path.exists(path): #文件夹存在就下载最新年报
            os.makedirs(path,exist_ok=True)
            #下载最新年报
            url1='http://vip.stock.finance.sina.com.cn/corp/go.php/vCB_Bulletin/stockid/%s/page_type/ndbg.phtml'%Code[-11:-5]
            driver.get(url1)
            html=BeautifulSoup(driver.page_source,"html.parser") 
            all_a=html.find('div',class_='datelist').find_all('a')
            title=all_a[0].get_text()
                #print(a['href'])
            print(title)
            url2='http://vip.stock.finance.sina.com.cn/'+all_a[0]['href']
            driver.get(url2)
            html=BeautifulSoup(driver.page_source,"html.parser")
            b=html.find('div',id='con02-7').find('a')
            url3=b['href']

            try:
                response = requests.get(url3)    
            except Exception as cuowu:
                print(cuowu.args)
                continue
            with open(path+'\\'+title+'.pdf', 'wb') as f:
                f.write(response.content)
        else:
            os.makedirs(path,exist_ok=True)
            #下载年报
            url1='http://vip.stock.finance.sina.com.cn/corp/go.php/vCB_Bulletin/stockid/%s/page_type/ndbg.phtml'%Code[-11:-5]
            driver.get(url1)
            html=BeautifulSoup(driver.page_source,"html.parser") 
            all_a=html.find('div',class_='datelist').find_all('a')
            for a in all_a:
                title=a.get_text()
                #print(a['href'])
                print(title)
                url2='http://vip.stock.finance.sina.com.cn/'+a['href']
                driver.get(url2)
                html=BeautifulSoup(driver.page_source,"html.parser")
                b=html.find('div',id='con02-7').find('a')
                url3=b['href']

                try:
                    response = requests.get(url3)    
                except Exception as cuowu:
                    print(cuowu.args)
                    continue
                with open(path+'\\'+title+'.pdf', 'wb') as f:
                     f.write(response.content)


        driver = webdriver.Chrome()
        url='https://emweb.securities.eastmoney.com/pc_hsf10/pages/index.html?type=web&code=%s&color=b#/cwfx'%code
        driver.get(url)
        #通过路径定位“按年度”读取利润表5年，并翻下一页再读取，然后合并数据

        time.sleep(5)#首次爬取不稳定！！！！！！！！！！！！！！！！！！！！！！！！！！！！
        driver.find_element(By.XPATH,'//*[@id="app"]/div[4]/div/div[3]/div[3]/div[1]/ul[2]/li[2]').click()#用相对路径定位资产负债表的“按年度”并点击
        time.sleep(3)
        tables = pd.read_html(StringIO(driver.page_source))
        data11 = tables[13]
        data11.columns =  list(tables[12].columns[0:])
        print(data11)
        for i in range(5):
            driver.find_element(By.XPATH,'//*[@id="app"]/div[4]/div/div[3]/div[3]/div[2]/i[2]').click()
            time.sleep(3)
        tables = pd.read_html(StringIO(driver.page_source))
        data12 = tables[13]
        data12.columns =list(tables[12].columns[0:])
        print(data12)
        col_use = data12.columns.difference(data11.columns)[::-1]
        data12=pd.concat([data12.iloc[:,0],data12[col_use]],axis=1)#去除重复列
        zcb=pd.merge(data11,data12,on='资产负债表',how='outer')
        zcb=zcb.set_index('资产负债表')#将第一列设为索引
        print(zcb)

        #通过路径定位“按年度”读取利润表5年，并翻下一页再读取，然后合并数据
        driver.find_element(By.XPATH,'//*[@id="app"]/div[4]/div/div[3]/div[2]/ul/li[2]').click()
        time.sleep(5)
        driver.find_element(By.XPATH,'//*[@id="app"]/div[4]/div/div[3]/div[4]/div[1]/ul[2]/li[2]').click()
        time.sleep(5)
        tables = pd.read_html(StringIO(driver.page_source))
        '''# 打印所有表格的基本信息，帮助确定资产负债表的索引
        for i, table in enumerate(tables):
            print(f"表格索引: {i}")
            print(table.head())
            print("-" * 50)
        '''   
        data21 = tables[15]
        print(data21)
        for i in range(5):
            driver.find_element(By.XPATH,'//*[@id="app"]/div[4]/div/div[3]/div[4]/div[2]/i[2]').click()
            time.sleep(3)
        tables = pd.read_html(StringIO(driver.page_source))
        data22 = tables[15]
        print(data22)
        col_use = data22.columns.difference(data21.columns)[::-1]
        data22=pd.concat([data22.iloc[:,0],data22[col_use]],axis=1)
        lrb=pd.merge(data21,data22,on='利润表',how='outer')
        lrb=lrb.set_index('利润表')#将第一列设为索引
        #通过路径定位“按年度”读取现金流量表5年，并翻下一页再读取，然后合并数据
        driver.find_element(By.XPATH,'//*[@id="app"]/div[4]/div/div[3]/div[2]/ul/li[3]').click()
        time.sleep(5)
        driver.find_element(By.XPATH,'//*[@id="app"]/div[4]/div/div[3]/div[5]/div[1]/ul[2]/li[2]').click()
        time.sleep(5)
        tables = pd.read_html(StringIO(driver.page_source))
        data31 = tables[17]
        print(data31)
        for i in range(5):
            driver.find_element(By.XPATH,'//*[@id="app"]/div[4]/div/div[3]/div[5]/div[2]/i[2]').click()
            time.sleep(3)
        tables = pd.read_html(StringIO(driver.page_source))
        data32 = tables[17]
        print(data32)
        col_use = data32.columns.difference(data31.columns)[::-1]
        data32=pd.concat([data32.iloc[:,0],data32[col_use]],axis=1)
        xjb=pd.merge(data31,data32,on='现金流量表',how='outer')
        xjb=xjb.set_index('现金流量表')#将第一列设为索引
        xjb.to_excel('现金流量表.xlsx')
        df=pd.concat([zcb,lrb,xjb]) 

        df=df.replace('--','0')#将数据中的‘--’替换成None
        df=df.fillna('0')#将空值填充为'0'字符
        df=df.drop('审计意见(境内)')#这行删除
        #df=df.drop_duplicates()#删除重复行,默认保留第一个出现的
        df.loc['财务费用（真）']=df.loc['财务费用'].iloc[0]
        #print(df.loc['财务费用（真）'])
        df=df.loc[~df.index.duplicated(keep='last')]#删除重复的索引名，取第二个
        #df=df[~df.columns.duplicated(keep='first')]
        #df=df.loc[:,~df.columns.duplicated(keep='first')]#删除重复列  
        if '利息收入' in df.index:
            df.loc['利息收入（财务）']=df.loc['利息收入']#清除了第一个利息收入后，利息收入只有一个，可能是总收入中的（作财务费用是低估），也可能是财务费用中的
            print(df.loc['利息收入（财务）'])
        elif '其中:利息收入' in df.index:
            df.loc['利息收入（财务）']=df.loc['其中:利息收入']#财务费用中的        
        else:
            df.loc['利息收入（财务）']='0'

        
        for i in df.index:
            for j in df.columns:
                if '万亿' in df.loc[i,j]:
                    df.loc[i,j]=eval(df.loc[i,j].replace('万亿', '*1000000000000'))
                elif '万' in df.loc[i,j]:
                    df.loc[i,j]=eval(df.loc[i,j].replace('万', '*10000'))
                elif '亿' in df.loc[i,j]:
                    df.loc[i,j]=eval(df.loc[i,j].replace('亿','*100000000'))
        df=df.astype('float64')#将数据转换成float64
        ZCB=pd.DataFrame(index=['货币资金','结算备付金','融出资金','拆出资金','交易性金融资产','以公允价值计量且其变动计入当期损益的金融资产','衍生金融资产','买入返售金融资产','持有待售资产','一年内到期的非流动资产','发放贷款及垫款','债权投资','其他债权投资','可供出售金融资产','持有至到期投资','投资性房地产','其他权益工具投资','其他非流动金融资产','金融资产合计：','长期股权投资','应收票据及应收账款','应收款项融资',
        '预付款项','其他应收款合计','存货','合同资产','其他流动资产','长期应收款','营运资产小计：','应付票据及应付账款','预收款项','合同负债','应付手续费及佣金','应付职工薪酬','应交税费','其他应付款合计','其他流动负债','长期应付职工薪酬','专项应付款','预计负债','递延收益','其他非流动负债','营运负债小计：','营运资本合计：','固定资产','在建工程','生产性生物资产','油气资产','使用权资产','无形资产','开发支出','商誉','长期待摊费用','递延所得税资产','其他非流动资产',
        '递延所得税负债','长期经营资产合计：','经营资产合计：','资产总额：','其中:应付利息','短期借款','向中央银行借款','吸收存款及同业存放','拆入资金','交易性金融负债','衍生金融负债','卖出回购金融资产款','短期应付债券','持有待售负债','一年内到期的非流动负债','短期债务合计：','长期借款','应付债券','租赁负债','长期应付款','长期债务合计：','有息债务合计：','应付股利','实收资本（或股本）','其他权益工具','资本公积','其他综合收益','减:库存股',
        '专项储备','盈余公积','一般风险准备','未分配利润','少数股东权益','股东权益合计：','少数股东权益比例','资本总额：','资产类别结构分析：','金融资产比例','长期股权比例','营运资产比例','长期经营资本比例','资本来源结构分析：','债务资金比例','股权资金比例','财务杠杆倍数','资产的管理效率和回报率分析：','资产周转率','经营资产周转率','长期资产周转率','营运资产周转率','营运资本的管理效率分析：','应收账款周转率','存货周转率','应付周转率','营业周期','现金周期'])
        ZCB=pd.merge(ZCB,df,left_index=True,right_index=True,how='left')
        ZCB=ZCB.fillna(0)#空值填充0,部分数据df中没有，merge后是空值不能运算
        ZCB.to_excel('ZCB.xlsx')
        print(ZCB)    

            #资产资本表分析，分为金融资产（JRZC）、营运资产（YYZC）、经营资产（JYZC）、短期债务（DQZW）、长期债务（CQZW）、股东权益（GDQY）、资产分析（ZCFX）等模块
        JRZC=ZCB.loc[['货币资金','结算备付金','融出资金','拆出资金','交易性金融资产','以公允价值计量且其变动计入当期损益的金融资产','衍生金融资产','买入返售金融资产','持有待售资产','一年内到期的非流动资产','发放贷款及垫款','债权投资','其他债权投资','可供出售金融资产','持有至到期投资','投资性房地产','其他权益工具投资','其他非流动金融资产']]#多行索引读取
        ZCB.loc['金融资产合计：']=JRZC.sum() #将每列的和，新增索引值为‘金融资产’的数据
        YYZC=ZCB.loc[['应收票据及应收账款','应收款项融资','预付款项','其他应收款合计','存货','合同资产','其他流动资产','长期应收款']]
        ZCB.loc['营运资产小计：']=YYZC.sum()
        YYFZ=ZCB.loc[['应付票据及应付账款','预收款项','合同负债','应付手续费及佣金','应付职工薪酬','应交税费','其他应付款合计','预计负债','其他流动负债','长期应付职工薪酬','专项应付款','递延收益','其他非流动负债']]
        ZCB.loc['营运负债小计：']=YYFZ.sum()
        ZCB.loc['营运资本合计：']=ZCB.loc['营运资产小计：'].sub(ZCB.loc['营运负债小计：'])#
        JYZC=ZCB.loc[['固定资产','在建工程','生产性生物资产','油气资产','使用权资产','无形资产','开发支出','商誉','长期待摊费用','递延所得税资产','其他非流动资产','递延所得税负债']]
        ZCB.loc['长期经营资产合计：']=JYZC.sum()
        ZCB.loc['长期经营资产合计：']=ZCB.loc['长期经营资产合计：'].sub(ZCB.loc['递延所得税负债']*2)
        ZCB.loc['经营资产合计：']=ZCB.loc['营运资本合计：'].add(ZCB.loc['长期经营资产合计：'])
        ZCB.loc['资产总额：']=ZCB.loc['金融资产合计：'].add(ZCB.loc['经营资产合计：']).add(ZCB.loc['长期股权投资'])
        DQZW=ZCB.loc[['其中:应付利息','短期借款','向中央银行借款','吸收存款及同业存放','拆入资金','交易性金融负债','衍生金融负债','卖出回购金融资产款','短期应付债券','持有待售负债','一年内到期的非流动负债']]
        ZCB.loc['短期债务合计：']=DQZW.sum()
        CQZW=ZCB.loc[['长期借款','应付债券','租赁负债','长期应付款']]
        ZCB.loc['长期债务合计：']=CQZW.sum()
        ZCB.loc['有息债务合计：']=ZCB.loc['短期债务合计：'].add(ZCB.loc['长期债务合计：'])
        GDQY=ZCB.loc[['应付股利','实收资本（或股本）','其他权益工具','资本公积','其他综合收益','减:库存股','专项储备','盈余公积','一般风险准备','未分配利润','少数股东权益']]
        ZCB.loc['股东权益合计：']=GDQY.sum()
        ZCB.loc['股东权益合计：']=ZCB.loc['股东权益合计：'].sub(ZCB.loc['减:库存股']*2)
        ZCB.loc['少数股东权益比例']=(ZCB.loc['少数股东权益'].div(ZCB.loc['股东权益合计：']))
        ZCB.loc['资本总额：']=ZCB.loc['有息债务合计：'].add(ZCB.loc['股东权益合计：'])
            #ZCFX=pd.DataFrame(columns=df.columns)#列数必须和df数据一致
        ZCB.loc['金融资产比例']=(ZCB.loc['金融资产合计：'].div(ZCB.loc['资产总额：']))
        ZCB.loc['长期股权比例']=(ZCB.loc['长期股权投资'].div(ZCB.loc['资产总额：']))
        ZCB.loc['营运资产比例']=(ZCB.loc['营运资本合计：'].div(ZCB.loc['资产总额：']))
        ZCB.loc['长期经营资本比例']=(ZCB.loc['长期经营资产合计：'].div(ZCB.loc['资产总额：']))
        ZCB.loc['债务资金比例']=(ZCB.loc['有息债务合计：'].div(ZCB.loc['资本总额：']))
        ZCB.loc['股权资金比例']=(ZCB.loc['股东权益合计：'].div(ZCB.loc['资本总额：']))
        ZCB.loc['财务杠杆倍数']=(ZCB.loc['资本总额：'].div(ZCB.loc['股东权益合计：'])).map(lambda x: '%.2f'%x).astype('float64')#转换成两位数,字符串格式，转换成数字格式
        ZCB.loc['资产周转率']=(df.loc['营业收入'].div(ZCB.loc['资产总额：'])).map(lambda x: '%.2f'%x).astype('float64')#转换成两位数,字符串格式，转换成数字格式    
        #计算周转率
        TA1=list()#不能用DataFrame
        TA2=list()
        TA3=list()
        for i in range(0,df.shape[1]-1):#列数必须和df数据一致,但遍历只能到df.shape[1]-1,如果加一循环中的i+1则超范围了
            TA1.append((ZCB.loc['应收票据及应收账款'].iloc[i]+ZCB.loc['应收票据及应收账款'].iloc[1+i]+ZCB.loc['应收款项融资'].iloc[i]+ZCB.loc['应收款项融资'].iloc[1+i]+ZCB.loc['合同资产'].iloc[i]+ZCB.loc['合同资产'].iloc[1+i]-ZCB.loc['预收款项'].iloc[i]-ZCB.loc['预收款项'].iloc[1+i]-ZCB.loc['合同负债'].iloc[i]-ZCB.loc['合同负债'].iloc[1+i])/2)
        TA1.extend([0])#再添加一个数字补齐列数
        ZCB.loc['应收账款周转率']=(df.loc['营业收入'].div(TA1)).map(lambda x: '%.2f'%x).astype('float64')#转换成两位数,字符串格式，转换成数字格式
        for i in range(0,df.shape[1]-1):
            TA2.append((ZCB.loc['存货'].iloc[i]+ZCB.loc['存货'].iloc[1+i])/2)
        TA2.extend([0])#再添加一个数字补齐列数
        ZCB.loc['存货周转率']=(df.loc['营业成本'].div(TA2)).map(lambda x: '%.2f'%x).astype('float64')#转换成两位数,字符串格式，转换成数字格式
        for i in range(0,df.shape[1]-1):
            TA3.append((ZCB.loc['应付票据及应付账款'].iloc[i]+ZCB.loc['应付票据及应付账款'].iloc[i+1]-ZCB.loc['预付款项'].iloc[i]-ZCB.loc['预付款项'].iloc[1+i])/2)
        TA3.extend([0])#再添加一个数字补齐列数
        ZCB.loc['应付周转率']=(df.loc['营业成本'].div(TA3)).map(lambda x: '%.2f'%x).astype('float64')#转换成两位数,字符串格式，转换成数字格式
        ZCB.loc['营业周期']=(360/ZCB.loc['应收账款周转率']+360/ZCB.loc['存货周转率']).map(lambda x: '%.2f'%x).astype('float64')#转换成两位数,字符串格式，转换成数字格式
        ZCB.loc['现金周期']=(ZCB.loc['营业周期']-360/ZCB.loc['应付周转率']).map(lambda x: '%.2f'%x).astype('float64')#转换成两位数,字符串格式，转换成数字格式



        XJB=pd.DataFrame(index=['投资活动产生的现金流分析：','购建和处置长期资产的投资决策和活动：','处置固定资产、无形资产和其他长期资产收回的现金净额','购建固定资产、无形资产和其他长期资产支付的现金','固定资产和投资性房地产折旧','无形资产摊销','长期待摊费用摊销','处置固定资产、无形资产和其他长期资产的损失','固定资产报废损失','长期资产新投资额：','长期资产扩张性资本支出比例：','取得和处置子公司的投资决策和活动：',
                                        '处置子公司及其他营业单位收到的现金','取得子公司及其他营业单位支付的现金净额','经营活动产生的现金流量净额','筹资活动产生的现金流量净额','净合并额：','综合现金需求：','现金自给率：','筹资需求：','筹资活动产生的现金流分析：','筹资活动中资金成本和资本结构分析:','吸收投资收到的现金','分配股利、利润或偿付利息支付的现金','其中:子公司支付给少数股东的股利、利润','取得借款收到的现金','发行债券收到的现金','偿还债务所支付的现金'])
        XJB=pd.merge(XJB,df,left_index=True,right_index=True,how='left')
        XJB=XJB.fillna(0)#空值填充0,部分数据df中没有，merge后是空值不能运算
            
            #投资筹资表分析，分为资产扩张（ZCKZ）、现金需求（XJXQ）、筹资活动（CZHD）三个模块。
        ZCKZ=XJB.loc[['处置固定资产、无形资产和其他长期资产收回的现金净额','购建固定资产、无形资产和其他长期资产支付的现金','固定资产和投资性房地产折旧','无形资产摊销','长期待摊费用摊销','处置固定资产、无形资产和其他长期资产的损失','固定资产报废损失']]
        XJB.loc['长期资产新投资额：']=XJB.loc['购建固定资产、无形资产和其他长期资产支付的现金']*2-ZCKZ.sum()
        TB1=list(ZCB.loc['长期经营资产合计：'].iloc[1:])#去掉首个数，其余赋予TB1，索引从1开始
        TB1.extend([0])#用0补齐数列
        XJB.loc['长期资产扩张性资本支出比例：']=(XJB.loc['长期资产新投资额：'].div(TB1))
        df.loc['扩张比例值']=(XJB.loc['长期资产扩张性资本支出比例：'].iloc[0]+XJB.loc['长期资产扩张性资本支出比例：'].iloc[1]+XJB.loc['长期资产扩张性资本支出比例：'].iloc[2])/3#近三年平均值
        XJB.loc['长期资产扩张性资本支出比例：']=(XJB.loc['长期资产新投资额：'].div(TB1))   
            #XJXQ=df.loc[['处置子公司及其他营业单位收到的现金','取得子公司及其他营业单位支付的现金净额','经营活动产生的现金流量净额','筹资活动产生的现金流量净额']]
        XJB.loc['净合并额：']=XJB.loc['取得子公司及其他营业单位支付的现金净额'].sub(XJB.loc['处置子公司及其他营业单位收到的现金'])
        df.loc['每年合并值']=(XJB.loc['净合并额：'].iloc[0]+XJB.loc['净合并额：'].iloc[1]+XJB.loc['净合并额：'].iloc[2])/3#近三年平均值
        XJB.loc['综合现金需求：']=XJB.loc['购建固定资产、无形资产和其他长期资产支付的现金'].sub(XJB.loc['处置固定资产、无形资产和其他长期资产收回的现金净额']).add(XJB.loc['净合并额：'])
        XJB.loc['现金自给率：']=(XJB.loc['经营活动产生的现金流量净额'].div(XJB.loc['综合现金需求：']))
        df.loc['现金自给率值']=(XJB.loc['现金自给率：'].iloc[0]+XJB.loc['现金自给率：'].iloc[1]+XJB.loc['现金自给率：'].iloc[2])/3#近三年平均值
        TB2=list(ZCB.loc['金融资产合计：'].iloc[1:])
        TB2.extend([0])
        XJB.loc['筹资需求：']=XJB.loc['经营活动产生的现金流量净额'].sub(XJB.loc['综合现金需求：']).add(TB2)

        LRB=pd.DataFrame(index=['营业收入','营业成本','营业税金及附加','销售费用','管理费用','研发费用','其他收益','资产减值损失','资产减值损失(新)','信用减值损失(新)','资产处置收益','加:营业外收入','减:营业外支出','毛利：','息税前经营利润：','投资收益','其中:对联营企业和合营企业的投资收益','加:公允价值变动收益','利息收入（财务）','归属于母公司股东的其他综合收益','归属于少数股东的其他综合收益','息税前金融资产收益：','息税前利润：','财务费用（真）','其中:利息费用','减:所得税','真实财务费用：','税前利润：',
                                        '实际所得税税率：','息前税后经营利润：','息前税后金融资产收益：','净利润：','股权价值增加值：','股东权益回报率（全面摊薄）：','营业收入和营业成本分析：','营业收入环比','营业成本环比','毛利润环比','净利润环比','毛利润率','净利润率','营业费用分析：','营业税金及附加率','销售费用率','管理费用率','研发费用率','资产减值损失率','营业外收入净额比率','总费用率','经营现金流分析：','收入现金含量','经营利润现金含量',
                                        '净利润现金含量','成本费用付现率','息税前利润构成分析：','长期投资利润占比','经营利润占比','金融资产利润占比','各部分利润率分析：','息前税后经营利润率','税后金融资产收益率','长期股权投资收益率','财务杠杆效应分析：','财务成本负担率','财务杠杆效应'])
        LRB=pd.merge(LRB,df,left_index=True,right_index=True,how='left')
        LRB=LRB.fillna(0)#空值填充0,部分数据df中没有，merge后是空值不能运算
        LRB.loc['毛利：']=df.loc['营业收入'].sub(df.loc['营业成本'])
        LRB.loc['息税前经营利润：']=LRB.loc['毛利：']+LRB.loc['其他收益']+LRB.loc['资产处置收益']+LRB.loc['资产减值损失(新)']+LRB.loc['信用减值损失(新)']+LRB.loc['加:营业外收入']-LRB.loc['营业税金及附加']-LRB.loc['销售费用']-LRB.loc['管理费用']-LRB.loc['研发费用']-LRB.loc['资产减值损失']-LRB.loc['减:营业外支出']
        LRB.loc['息税前金融资产收益：']=LRB.loc['投资收益']+LRB.loc['加:公允价值变动收益']+LRB.loc['利息收入（财务）']+LRB.loc['归属于母公司股东的其他综合收益']+LRB.loc['归属于少数股东的其他综合收益']-LRB.loc['其中:对联营企业和合营企业的投资收益']
        LRB.loc['真实财务费用：']=LRB.loc['利息收入（财务）']+LRB.loc['财务费用（真）']
        LRB.loc['息税前利润：']=LRB.loc['息税前经营利润：']+LRB.loc['息税前金融资产收益：']+LRB.loc['其中:对联营企业和合营企业的投资收益']
        LRB.loc['税前利润：']=LRB.loc['息税前利润：'].sub(LRB.loc['真实财务费用：'])
        LRB.loc['实际所得税税率：']=(LRB.loc['减:所得税']/(LRB.loc['税前利润：']-LRB.loc['其中:对联营企业和合营企业的投资收益']))
        LRB.loc['息前税后经营利润：']=LRB.loc['息税前经营利润：']*(1-LRB.loc['实际所得税税率：'])
        LRB.loc['息前税后金融资产收益：']=LRB.loc['息税前金融资产收益：']-((LRB.loc['息税前金融资产收益：'])*LRB.loc['实际所得税税率：'])
        LRB.loc['净利润：']=LRB.loc['税前利润：'].sub(LRB.loc['减:所得税'])
        LRB.loc['股权价值增加值：']=LRB.loc['净利润：'].sub(ZCB.loc['股东权益合计：']*0.08)#股东预期收益及成本假设为0.08
        LRB.loc['股东权益回报率（全面摊薄）：']=(LRB.loc['净利润：'].div(ZCB.loc['股东权益合计：']))
        df.loc['ROE值']=(LRB.loc['股东权益回报率（全面摊薄）：'].iloc[0]+LRB.loc['股东权益回报率（全面摊薄）：'].iloc[1]+LRB.loc['股东权益回报率（全面摊薄）：'].iloc[2])/3#近三年平均值
        LRB.loc['股东权益回报率（全面摊薄）：']=(LRB.loc['净利润：'].div(ZCB.loc['股东权益合计：']))
                
        TA4=list()#不能用DataFrame
        TA5=list()
        TA6=list()
        TA7=list()
        for i in range(0,df.shape[1]-1):
            c=LRB.loc['毛利：'].iloc[1+i]
            d=LRB.loc['净利润：'].iloc[1+i]
            if c==0:
                TA6.append(0)
            else:
                TA6.append((LRB.loc['毛利：'].iloc[i] - LRB.loc['毛利：'].iloc[i + 1]) / c)
            if d==0:
                TA7.append(0)
            else:
                TA7.append((LRB.loc['净利润：'].iloc[i] - LRB.loc['净利润：'].iloc[i + 1]) / d)    
            TA4.append((df.loc['营业收入'].iloc[i]-df.loc['营业收入'].iloc[1+i])/df.loc['营业收入'].iloc[1+i])
            TA5.append((df.loc['营业成本'].iloc[i]-df.loc['营业成本'].iloc[1+i])/df.loc['营业成本'].iloc[1+i])
        TA4.extend([0])#再添加一个数字补齐列数
        TA5.extend([0])#再添加一个数字补齐列数
        TA6.extend([0])#再添加一个数字补齐列数
        TA7.extend([0])#再添加一个数字补齐列数
        LRB.loc['营业收入环比']=TA4
        LRB.loc['营业成本环比']=TA5
        LRB.loc['毛利润环比']=TA6
        LRB.loc['净利润环比']=TA7
        LRB.loc['毛利润率']=(LRB.loc['毛利：'].div(df.loc['营业收入']))
        LRB.loc['净利润率']=(LRB.loc['净利润：'].div(df.loc['营业收入']))
        df.loc['毛利率值']=(LRB.loc['毛利润率'].iloc[0]+LRB.loc['毛利润率'].iloc[1]+LRB.loc['毛利润率'].iloc[2])/3#近三年平均值
        df.loc['净利率值']=(LRB.loc['净利润率'].iloc[0]+LRB.loc['净利润率'].iloc[1]+LRB.loc['净利润率'].iloc[2])/3#近三年平均值
        LRB.loc['毛利润率']=(LRB.loc['毛利：'].div(df.loc['营业收入']))
        LRB.loc['净利润率']=(LRB.loc['净利润：'].div(df.loc['营业收入']))
                #费用率先算总费用再加格式
        LRB.loc['营业税金及附加率']=(LRB.loc['营业税金及附加'].div(df.loc['营业收入']))
        LRB.loc['销售费用率']=(LRB.loc['销售费用'].div(df.loc['营业收入']))
        LRB.loc['管理费用率']=(LRB.loc['管理费用'].div(df.loc['营业收入']))
        LRB.loc['研发费用率']=(LRB.loc['研发费用'].div(df.loc['营业收入']))
        df.loc['研发费用率值']=(LRB.loc['研发费用率'].iloc[0]+LRB.loc['研发费用率'].iloc[1]+LRB.loc['研发费用率'].iloc[2])/3#近三年平均值
        df.loc['五年复合增长率值']=(df.loc['营业收入'].iloc[0]/df.loc['营业收入'].iloc[4])**(1/4)-1  #近五年营收复合增长率
        LRB.loc['资产减值损失率']=((LRB.loc['资产减值损失']+LRB.loc['资产减值损失(新)']+LRB.loc['信用减值损失(新)'])/df.loc['营业收入'])
        LRB.loc['营业外收入净额比率']=((LRB.loc['其他收益']+LRB.loc['资产处置收益']+LRB.loc['加:营业外收入']-LRB.loc['减:营业外支出'])/df.loc['营业收入'])
        LRB.loc['总费用率']=(LRB.loc['营业税金及附加率']+LRB.loc['销售费用率']+LRB.loc['管理费用率']+LRB.loc['研发费用率']+LRB.loc['资产减值损失率']-LRB.loc['营业外收入净额比率'])  
        LRB.loc['收入现金含量']=(df.loc['销售商品、提供劳务收到的现金']/1.17/df.loc['营业收入']).map(lambda x: '%.2f'%x).astype('float64')#假设营业增值税率17%
        LRB.loc['经营利润现金含量']=(df.loc['经营活动产生的现金流量净额']/LRB.loc['息前税后经营利润：']).map(lambda x: '%.2f'%x).astype('float64')
        LRB.loc['净利润现金含量']=(df.loc['经营活动产生的现金流量净额'].div(LRB.loc['净利润：'])).map(lambda x: '%.2f'%x).astype('float64')
        df.loc['净利润现金含量值']=(LRB.loc['净利润现金含量'].iloc[0]+LRB.loc['净利润现金含量'].iloc[1]+LRB.loc['净利润现金含量'].iloc[2])#近三年和
        LRB.loc['长期投资利润占比']=(LRB.loc['其中:对联营企业和合营企业的投资收益'].div(LRB.loc['息税前利润：']))
        LRB.loc['经营利润占比']=(LRB.loc['息税前经营利润：'].div(LRB.loc['息税前利润：']))
        LRB.loc['金融资产利润占比']=(LRB.loc['息税前金融资产收益：'].div(LRB.loc['息税前利润：']))
        LRB.loc['息前税后经营利润率']=(LRB.loc['息前税后经营利润：']/df.loc['营业收入'])
        LRB.loc['税后金融资产收益率']=(LRB.loc['息前税后金融资产收益：']/ZCB.loc['金融资产合计：'])
        for i in range(len(ZCB.loc['长期股权投资'])):
            if ZCB.loc['长期股权投资',ZCB.columns[i]]!=0:
                LRB.loc['长期股权投资收益率',LRB.columns[i]]=LRB.loc['其中:对联营企业和合营企业的投资收益',LRB.columns[i]]/(ZCB.loc['长期股权投资',LRB.columns[i]])
            else:
                LRB.loc['长期股权投资收益率', LRB.columns[i]] = 0
                
                #LRB.loc['长期股权投资收益率']=LRB.loc['长期股权投资收益率'].map(lambda x: '%.2f%%'%(x*100))
        LRB.loc['财务成本负担率']=(LRB.loc['真实财务费用：'].div(LRB.loc['息税前利润：']))
        df.loc['财务成本负担值']=(LRB.loc['财务成本负担率'].iloc[0]+LRB.loc['财务成本负担率'].iloc[1]+LRB.loc['财务成本负担率'].iloc[2])/3#近三年平均值
        LRB.loc['财务杠杆效应']=((1-LRB.loc['财务成本负担率'])*ZCB.loc['财务杠杆倍数']).map(lambda x: '%.2f'%x).astype('float64')
                
            
                #估值表
        GZ=pd.DataFrame(columns=df.columns)#列数必须和df数据一致
        GZ=XJB.loc[['经营活动产生的现金流量净额','固定资产和投资性房地产折旧','无形资产摊销','长期待摊费用摊销','处置固定资产、无形资产和其他长期资产的损失','固定资产报废损失']]
        GZ.loc['资产减值准备']=df.loc['资产减值准备']
        GZ.loc['F0']=(XJB.loc['经营活动产生的现金流量净额']*2)-GZ.sum()
            
                #print(GZ.loc['F0'])
        GZ2=ZCB.loc[['长期股权投资','金融资产合计：','有息债务合计：','少数股东权益比例']]
        GZ=pd.concat([GZ,GZ2])
                
      
        with pd.ExcelWriter(path+'\\'+name+'.xlsx', mode="w+") as writer:
            ZCB.to_excel(writer,sheet_name='资产负债表')
            XJB.to_excel(writer,sheet_name='投资筹资表')
            LRB.to_excel(writer,sheet_name='股权价值增加表')
            GZ.to_excel(writer,sheet_name='估值')
            df.to_excel(writer,sheet_name='df')
            zcb.to_excel(writer,sheet_name='zcb')
            lrb.to_excel(writer,sheet_name='lrb')
            xjb.to_excel(writer,sheet_name='xjb')
        wb=opp.load_workbook(path+'\\'+name+'.xlsx')
        ws3=wb['估值']
        ws3.append(['总股本:',Pr,ZCB.loc['实收资本（或股本）'].iloc[1],ZCB.loc['实收资本（或股本）'].iloc[2]])
        ws3.append(["FCF0","=AVERAGE(B9,C9,D9)","=AVERAGE(C9,D9,E9)","=AVERAGE(D9,E9,F9)"])#15行
        ws3.append(["三段：10,6,2",
                            "=B15*((1+0.1)/1.08)*(1-POWER((1+0.1)/1.08,5))/(1-(1+0.1)/1.08)+B15*POWER((1+0.1)/1.08,5)*((1+0.06)/1.08)*(1-POWER((1+0.06)/1.08,5))/(1-(1+0.06)/1.08)+B15*POWER((1+0.1),5)*POWER((1+0.06),5)*(1+0.02)/(0.08-0.02)/POWER(1.08,10)",
                            "=C15*((1+0.1)/1.08)*(1-POWER((1+0.1)/1.08,5))/(1-(1+0.1)/1.08)+C15*POWER((1+0.1)/1.08,5)*((1+0.06)/1.08)*(1-POWER((1+0.06)/1.08,5))/(1-(1+0.06)/1.08)+C15*POWER((1+0.1),5)*POWER((1+0.06),5)*(1+0.02)/(0.08-0.02)/POWER(1.08,10)",
                            "=D15*((1+0.1)/1.08)*(1-POWER((1+0.1)/1.08,5))/(1-(1+0.1)/1.08)+D15*POWER((1+0.1)/1.08,5)*((1+0.06)/1.08)*(1-POWER((1+0.06)/1.08,5))/(1-(1+0.06)/1.08)+D15*POWER((1+0.1),5)*POWER((1+0.06),5)*(1+0.02)/(0.08-0.02)/POWER(1.08,10)"])#16行
        ws3.append(["两段：6,2",
                            "=B15*((1+0.06)/1.08)*(1-POWER((1+0.06)/1.08,5))/(1-(1+0.06)/1.08)+B15*POWER((1+0.06),5)*(1+0.02)/(0.08-0.02)/POWER((1+0.08),5)",
                            "=C15*((1+0.06)/1.08)*(1-POWER((1+0.06)/1.08,5))/(1-(1+0.06)/1.08)+C15*POWER((1+0.06),5)*(1+0.02)/(0.08-0.02)/POWER((1+0.08),5)",
                            "=D15*((1+0.06)/1.08)*(1-POWER((1+0.06)/1.08,5))/(1-(1+0.06)/1.08)+D15*POWER((1+0.06),5)*(1+0.02)/(0.08-0.02)/POWER((1+0.08),5)"])#17行
        ws3.append(["零增长：0","=B15/0.08","=C15/0.08","=D15/0.08"])#18行
        ws3.append(["三段每股现值","=(B16+B10+B11-B12)*(1-B13)/B14","=(C16+C10+C11-C12)*(1-C13)/C14","=(D16+D10+D11-D12)*(1-D13)/D14"])#19行
        ws3.append(["两段每股现值","=(B17+B10+B11-B12)*(1-B13)/B14","=(C17+C10+C11-C12)*(1-C13)/C14","=(D17+D10+D11-D12)*(1-D13)/D14"])  #20行    
        ws3.append(["零增长每股现值","=(B18+B10+B11-B12)*(1-B13)/B14","=(C18+C10+C11-C12)*(1-C13)/C14","=(D18+D10+D11-D12)*(1-D13)/D14"])#21行
        for s in list([19,20,21]):
            for j in list([2,3,4]):
                ws3.cell(s,j).number_format='0.00'
                
        ws3.merge_cells('A22:A32')
        ws3.cell(22,1,"评分(多用五年平均值)").alignment=Alignment(horizontal='center',vertical='center')#.font=Font(bold=True)
        ws3.merge_cells('B22:B26')
        ws3.cell(22,2,"成长性(0~9.5)").alignment=Alignment(horizontal='center',vertical='center')#.font=Font(bold=True)
        ws3.cell(22,3,"扩张")
        ws3.cell(22,4,"=AVERAGE(投资筹资表!B12:D12)").number_format='0.00%'
        ws3.cell(22,5,"=LOOKUP(D22,{-0.2,0.09,0.2,0.3},{0,1,2,3})")
        ws3.merge_cells('F22:F26')
        ws3.cell(22,6,"=SUM(E22:E26)").alignment=Alignment(horizontal='center',vertical='center')
        ws3.cell(23,3,"并购额大于3千万")
        ws3.cell(23,4,"=AVERAGE(投资筹资表!B18:D18)").number_format='0.00'
        ws3.cell(23,5,"=IF(D23>30000000,1,0)")
        ws3.cell(24,3,"现金自给率")
        ws3.cell(24,4,"=AVERAGE(投资筹资表!B20:D20)").number_format='0.00%'
        ws3.cell(24,5,"=IF(D24>1,1,0)")
        ws3.cell(25,3,"研发费用率")
        ws3.cell(25,4,"=AVERAGE(股权价值增加表!B47:D47)").number_format='0.00%'
        ws3.cell(25,5,"=IF(D25>0.04,0.5,0)")
        ws3.cell(26,3,"营收符合增长率")
        ws3.cell(26,4,"=(股权价值增加表!B2/股权价值增加表!F2)^(1/4)-1").number_format='0.00%'
        ws3.cell(26,5,"=LOOKUP(D26,{-0.5,0.08,0.18,0.3,0.4},{0,1,2,3,4})")
        ws3.merge_cells('B27:B30')
        ws3.cell(27,2,"营利能力(0~16)").alignment=Alignment(horizontal='center',vertical='center')#.font=Font(bold=True)        
        ws3.cell(27,3,"ROE")
        ws3.cell(27,4,"=AVERAGE(股权价值增加表!B35:F35)").number_format='0.00%'
        ws3.cell(27,5,"=LOOKUP(D27,{-0.5,0.1,0.15,0.2,0.25,0.3},{0,1,2,3,4,5})")
        ws3.merge_cells('F27:F30')
        ws3.cell(27,6,"=SUM(E27:E30)").alignment=Alignment(horizontal='center',vertical='center')
        ws3.cell(28,3,"毛利率")
        ws3.cell(28,4,"=AVERAGE(股权价值增加表!B41:F41)").number_format='0.00%'
        ws3.cell(28,5,"=LOOKUP(D28,{-0.5,0.14,0.25,0.3,0.35,0.4},{0,1,2,3,4,5})")
        ws3.cell(29,3,"净利率")
        ws3.cell(29,4,"=AVERAGE(股权价值增加表!B42:F42)").number_format='0.00%'
        ws3.cell(29,5,"=LOOKUP(D29,{-0.5,0.08,0.13,0.18,0.23,0.28},{0,1,2,3,4,5})")
        ws3.cell(30,3,"净利润现金含量")
        ws3.cell(30,4,"=SUM(股权价值增加表!B54:F54)").number_format='0.00'
        ws3.cell(30,5,"=IF(D30>6,1,IF(D30<3,-1,0))")
        ws3.cell(31,2,"财务成本(-4~0)").alignment=Alignment(horizontal='center',vertical='center')#.font=Font(bold=True)
        ws3.cell(31,3,"成本负担率")
        ws3.cell(31,4,"=AVERAGE(股权价值增加表!B65:D65)").number_format='0.00%'
        ws3.cell(31,5,"=LOOKUP(D31,{-0.5,0.08,0.18,0.25,0.35},{0,0.5,1,2,3})")
        ws3.cell(31,6,"=-E31")
        ws3.cell(32,2,"评分总计：").font=Font(bold=True)
        ws3.cell(32,6,"=SUM(F22:F31)").font=Font(bold=True)


                
                #边框
        side_type=Side(style='thin',color='000000')
        border=Border(left=side_type,right=side_type,top=side_type,bottom=side_type)
        for i in range(1,ws3.max_column+1):
            for t in range(1,ws3.max_row+1):
                ws3.cell(t,i).border=border  
        for i in range(66,76):
            i=chr(i)
            ws3.column_dimensions[i].width=15
        ws3.column_dimensions['A'].width=25

        ws0=wb['资产负债表']
                #更改列宽
        for i in range(66,76):
            i=chr(i)
            ws0.column_dimensions[i].width=12
        ws0.column_dimensions['A'].width=25
        for j in range(1,ws0.max_column+1): #不用循环可以使用ws0.row_dimensions[20]..fill=PatternFill(fill_type='solid',fgColor="1E90FF")
            ws0.cell(20,j).fill=PatternFill(fill_type='solid',fgColor="1E90FF")    
            ws0.cell(45,j).fill=PatternFill(fill_type='solid',fgColor="3CB371")
            ws0.cell(58,j).fill=PatternFill(fill_type='solid',fgColor="90EE90") 
            ws0.cell(60,j).font=Font(bold=True)
            ws0.cell(78,j).fill=PatternFill(fill_type='solid',fgColor="FFFF00")    
            ws0.cell(90,j).fill=PatternFill(fill_type='solid',fgColor="FFD700")    
            ws0.cell(92,j).font=Font(bold=True)
            for k in list([30,44,72,77,94,95,96,97,99,100,101,103,104,105,106,108,109,110,111,112]):
                ws0.cell(k,j).font=Font(color="3CB371")
            for s in list([91,94,95,96,97,99,100]):
                ws0.cell(s,j).number_format='0.00%'
            for t in range(1,ws0.max_row+1):
                ws0.cell(t,j).border=border
                        
        ws1=wb['投资筹资表']
                #更改列宽
        for i in range(66,76):
            i=chr(i)
            ws1.column_dimensions[i].width=12
        ws1.column_dimensions['A'].width=40

        for j in range(1,ws1.max_column+1):
            ws1.cell(3,j).fill=PatternFill(fill_type='solid',fgColor="6495ED")
            ws1.cell(12,j).number_format='0.00%'
            ws1.cell(13,j).fill=PatternFill(fill_type='solid',fgColor="6495ED")
            ws1.cell(23,j).fill=PatternFill(fill_type='solid',fgColor="98FB98") 
            ws1.cell(20,j).number_format='0.00%'
            for k in list([11,12,18,19,20,21]):
                ws1.cell(k,j).font=Font(color="3CB371")
            for t in range(1,ws1.max_row+1):
                ws1.cell(t,j).border=border

        ws2=wb['股权价值增加表']
                #  更改列宽
        for i in range(66,76):
            i=chr(i)
            ws2.column_dimensions[i].width=12
        ws2.column_dimensions['A'].width=30

        for j in range(1,ws2.max_column+1):
            ws2.cell(16,j).fill=PatternFill(fill_type='solid',fgColor="1E90FF")    
            ws2.cell(23,j).fill=PatternFill(fill_type='solid',fgColor="1E90FF")
            ws2.cell(24,j).fill=PatternFill(fill_type='solid',fgColor="1E90FF")
            ws2.cell(31,j).fill=PatternFill(fill_type='solid',fgColor="87CEEB")
            ws2.cell(32,j).fill=PatternFill(fill_type='solid',fgColor="87CEEB")
            ws2.cell(33,j).fill=PatternFill(fill_type='solid',fgColor="00FF00")
            ws2.cell(35,j).fill=PatternFill(fill_type='solid',fgColor="FFFF00")
            ws2.cell(24,j).font=Font(bold=True)
            ws2.cell(33,j).font=Font(bold=True)
            ws2.cell(35,j).font=Font(bold=True) 
            for k in list([15,28,29,30,34,37,38,39,40,41,42,44,45,46,47,48,49,50,52,53,54,55,57,58,59,61,62,63,65,66]):
                ws2.cell(k,j).font=Font(color="3CB371")
            for s in list([30,35,37,38,39,40,41,42,44,45,46,47,48,49,50,57,58,59,61,62,63,65]):
                ws2.cell(s,j).number_format='0.00%'
            for t in range(1,ws2.max_row+1):
                ws2.cell(t,j).border=border
        
        wb.save(path+'\\'+name+'（2024）.xlsx')
        wb.close()
    except Exception as cuowu:
        print(cuowu.args)
        continue    
                